const usersSql = 'SELECT * FROM users'
// 查询表是否存在
const checkTableSql = `
    SELECT COUNT(*)
    FROM information_schema.tables 
    WHERE table_schema = 'magic-node' 
    AND table_name = 'users'
`
// 写入数据
const createUserSql = 'INSERT INTO users (username, email, phone_number, password) VALUES (?, ?, ?, ?)';
// 创建表结构
const ensureUsersSql = `
    CREATE TABLE IF NOT EXISTS \`users\` (
        \`id\` INT AUTO_INCREMENT PRIMARY KEY,
        \`username\` VARCHAR(255) NOT NULL,
        \`email\` VARCHAR(255) NOT NULL,
        \`phone_number\` VARCHAR(20) NOT NULL,
        \`password\` VARCHAR(255) NOT NULL,
        \`created_at\` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        \`updated_at\` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
    )
`
// 检查用户是否存在
const isUsernameTakenSql = 'SELECT 1 FROM users WHERE username = ?';
// 检查邮箱是否存在
const isEmailTakenSql = 'SELECT 1 FROM users WHERE email = ?'

// 输入账号密码登录
const loginSql = ' SELECT * FROM users WHERE email = ?';
// 修改密码
const updatePasswordSql = 'UPDATE users SET password = ? WHERE email = ?';

module.exports = {
    usersSql,
    createUserSql,
    ensureUsersSql,
    checkTableSql,
    isUsernameTakenSql,
    isEmailTakenSql,
    loginSql,
    updatePasswordSql
}